﻿

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[V_UDF_CUSTOMER]
AS
SELECT   A.FCUSTID AS AUTOID, A.FCREATORID AS CREATOR, A.FCREATEDATE AS CREATETIME, A.FMODIFIERID AS MODIFIER, 
                A.FMODIFYDATE AS MODIFYTIME, A.FAPPROVERID AS APPROVER, A.FAPPROVEDATE AS APPROVETIME, 1 AS FLAG, 
                0 AS SITE, B.FNAME AS NAME, A.FNUMBER AS CODE, A.FTEL AS TEL, A.FADDRESS AS ADDRESS, 1 AS SALES, 
                1 AS CREATEDEPARTMENT, 1 AS MODIFYDEPARTMENT, 1 AS APPROVEDEPARTMENT, 
                A.FSALDEPTID AS DEPARTMENT
FROM      dbo.T_BD_CUSTOMER AS A LEFT OUTER JOIN
                dbo.T_BD_CUSTOMER_L AS B ON A.FCUSTID = B.FCUSTID AND B.FLOCALEID = 2052
WHERE   (A.FCREATEORGID = A.FUSEORGID)
GO


CREATE VIEW [dbo].[V_UDF_DEPARTMENT]
AS
SELECT   A.FCREATORID AS CREATOR, A.FCREATEDATE AS CREATETIME, A.FMODIFIERID AS MODIFIER, 
                A.FMODIFYDATE AS MODIFYTIME, A.FAUDITORID AS APPROVER, A.FAUDITDATE AS APPROVETIME, 1 AS FLAG, 
                0 AS SITE, A.FDEPTID AS AUTOID, A.FNUMBER AS CODE, B.FNAME AS NAME, A.FPARENTID AS PARENT, 
                (CASE A.FISROOT WHEN 1 THEN 1 ELSE 0 END) AS ROOT
FROM      dbo.T_BD_DEPARTMENT AS A LEFT OUTER JOIN
                dbo.T_BD_DEPARTMENT_L AS B ON A.FDEPTID = B.FDEPTID AND B.FLOCALEID = 2052
WHERE   (A.FCREATEORGID = A.FUSEORGID)
GO

CREATE VIEW [dbo].[V_UDF_MATERIAL]
AS
SELECT   A.FMATERIALID AS AUTOID, A.FCREATORID AS CREATOR, A.FCREATEDATE AS CREATETIME, 
                A.FMODIFIERID AS MODIFIER, A.FMODIFYDATE AS MODIFYTIME, A.FAPPROVERID AS APPROVER, 
                A.FAPPROVEDATE AS APPROVETIME, 1 AS FLAG, 0 AS SITE, B.FNAME AS NAME, A.FNUMBER AS CODE, 
                A.FMNEMONICCODE AS SHORTCODE, B.FSPECIFICATION AS SPECIFICATION, E.FNAME AS UNIT
FROM      dbo.T_BD_MATERIAL AS A LEFT OUTER JOIN
                dbo.T_BD_MATERIAL_L AS B ON B.FMATERIALID = A.FMATERIALID AND B.FLOCALEID = 2052 LEFT OUTER JOIN
                dbo.T_BD_MATERIALBASE AS C ON C.FMATERIALID = A.FMATERIALID LEFT OUTER JOIN
                dbo.T_BD_MATERIALSALE AS D ON D.FMATERIALID = A.FMATERIALID LEFT OUTER JOIN
                dbo.T_BD_UNIT_L AS E ON D.FSALEUNITID = E.FUNITID AND E.FLOCALEID = 2052
WHERE   (C.FISSALE = '1') AND (A.FCREATEORGID = A.FUSEORGID)
GO

CREATE VIEW [dbo].[V_UDF_SALESREPORT]
AS
SELECT   B.FENTRYID, A.FBILLNO, B.FSEQ, A.FCUSTID, C.FNUMBER AS CUSCODE, D.FNAME AS CUSNAME, A.FAPPROVEDATE, 
                E.FNUMBER AS MTLCODE, F.FNAME AS MTLNAME, B.FQTY, I.FNAME AS UNITNAME, G.FSTOCKOUTQTY, 
                G.FRETURNQTY, G.FREMAINOUTQTY, G.FARAMOUNT
FROM      dbo.T_SAL_ORDER AS A LEFT OUTER JOIN
                dbo.T_SAL_ORDERENTRY AS B ON B.FID = A.FID LEFT OUTER JOIN
                dbo.T_BD_CUSTOMER AS C ON C.FCUSTID = A.FCUSTID LEFT OUTER JOIN
                dbo.T_BD_CUSTOMER_L AS D ON A.FCUSTID = D.FCUSTID AND D.FLOCALEID = 2052 LEFT OUTER JOIN
                dbo.T_BD_MATERIAL AS E ON B.FMATERIALID = E.FMATERIALID LEFT OUTER JOIN
                dbo.T_BD_MATERIAL_L AS F ON E.FMATERIALID = F.FMATERIALID AND F.FLOCALEID = 2052 LEFT OUTER JOIN
                dbo.T_SAL_ORDERENTRY_R AS G ON G.FENTRYID = B.FENTRYID LEFT OUTER JOIN
                dbo.T_BD_UNIT_L AS I ON I.FUNITID = B.FUNITID AND I.FLOCALEID = 2052
GO